﻿namespace REVISIONMAX20
{
    using MySql.Data.MySqlClient;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SQLite;
    using System.Diagnostics;
    using System.IO;
    using System.Runtime.CompilerServices;
    using System.Text;

    public static class Common
    {
        public static readonly string _ConnectionStringDeleteCommand = "Delete `connectionstrings` where `id` = @id and `parentid` = @parentid";
        public static readonly string _ConnectionStringInsertCommand = "insert into `connectionstrings` (`id`, `created`, `updated`, `parentid`, `name`, `value`) VALUES (@id,@created,@updated,@parentid,@name,@value)";
        public static readonly string _ConnectionStringUpdateCommand = "update `connectionstrings` set `name` = @name, `value` = @value , `created` = @created, `updated` = @updated where `id` = @id";
        public static readonly string _GroupDeleteCommand = "Delete `groups` where `id` = @id";
        public static readonly string _GroupInsertCommand = "insert into `groups` (`id`, `created`, `updated`, `name`, `lastrevision`) VALUES (@id,@created,@updated,@name,@lastrevision)";
        public static readonly string _GroupUpdateCommand = "update `groups` set `name` = @name, `lastrevision` = @lastrevision, `created` = @created, `updated` = @updated where `id` = @id";
        public static readonly string _LogDeleteCommand = "Delete `logs` where `id` = @id and `parentid` = @parentid";
        public static readonly string _LogInsertCommand = "insert into `logs` (`id`, `created`, `updated`, `parentid`, `value`) VALUES (@id,@created,@updated,@parentid,@value)";
        public static readonly string _SourceDeleteCommand = "Delete `sources` where `id` = @id and `parentid` = @parentid";
        public static readonly string _SourceInsertCommand = "insert into `sources` (`id`, `created`, `updated`, `parentid`, `source`, `regex`, `recursive`) VALUES (@id,@created,@updated,@parentid,@source,@regex,@recursive)";
        public static readonly string _SourceUpdateCommand = "update `sources` set `source` = @source, `regex` = @regex, `recursive` = @recursive, `created` = @created, `updated` = @updated where `id` = @id";
        public static readonly string _SQLItemDeleteCommand = "Delete s`qlitems` where `id` = @id and `parentid` = @parentid";
        public static readonly string _SQLItemInsertCommand = "insert into `sqlitems` (`id`, `parentid`, `created`, `updated`, `index`, `revision`, `source`, `sourcetype`, `connectionstringid`, `executed`, `expired`) VALUES (@id,@parentid, @created,@updated,@index,@revision,@source,@sourcetype,@connectionstringid,@executed,@expired)";
        public static readonly string _SQLItemUpdateCommand = "update `sqlitems` set `index` = @index, `revision` = @revision, `source` = @source, `sourcetype` = @sourcetype, `connectionstringid` = @connectionstringid, `created` = @created, `updated` = @updated, `executed` = @executed, `expired` = @expired where `id` = @id and `parentid` = @parentid";
        public static readonly string SQLiteDBName = "REVISIONMAX20DB.sqlite";

        public static SQLiteCommand CreateConnectionStringCommand(this SQLiteConnection con)
        {
            SQLiteCommand command = con.CreateCommand();
            SQLiteParameter parameter = new SQLiteParameter("@name");
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);
            SQLiteParameter parameter2 = new SQLiteParameter("@value");
            parameter2.DbType = DbType.String;
            command.Parameters.Add(parameter2);
            return command;
        }

        public static SQLiteCommand CreateGroupCommand(this SQLiteConnection con)
        {
            SQLiteCommand command = con.CreateCommand();
            SQLiteParameter parameter = new SQLiteParameter("@name");
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);
            SQLiteParameter parameter2 = new SQLiteParameter("@lastrevision");
            parameter2.DbType = DbType.String;
            command.Parameters.Add(parameter2);
            return command;
        }

        public static SQLiteCommand CreateLogCommand(this SQLiteConnection con)
        {
            SQLiteCommand command = con.CreateCommand();
            SQLiteParameter parameter = new SQLiteParameter("@value");
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);
            return command;
        }

        public static SQLiteCommand CreateSourceCommand(this SQLiteConnection con)
        {
            SQLiteCommand command = con.CreateCommand();
            SQLiteParameter parameter = new SQLiteParameter("@source");
            parameter.DbType = DbType.String;
            command.Parameters.Add(parameter);
            SQLiteParameter parameter2 = new SQLiteParameter("@regex");
            parameter2.DbType = DbType.String;
            command.Parameters.Add(parameter2);
            SQLiteParameter parameter3 = new SQLiteParameter("@recursive");
            parameter3.DbType = DbType.Boolean;
            command.Parameters.Add(parameter3);
            return command;
        }

        public static SQLiteCommand CreateSQLItemCommand(this SQLiteConnection con)
        {
            SQLiteCommand command = con.CreateCommand();
            SQLiteParameter parameter = new SQLiteParameter("@index");
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);
            SQLiteParameter parameter2 = new SQLiteParameter("@revision");
            parameter2.DbType = DbType.String;
            command.Parameters.Add(parameter2);
            SQLiteParameter parameter3 = new SQLiteParameter("@source");
            parameter3.DbType = DbType.String;
            command.Parameters.Add(parameter3);
            SQLiteParameter parameter4 = new SQLiteParameter("@sourcetype");
            parameter4.DbType = DbType.String;
            command.Parameters.Add(parameter4);
            SQLiteParameter parameter5 = new SQLiteParameter("@connectionstringid");
            parameter5.DbType = DbType.Int32;
            command.Parameters.Add(parameter5);
            SQLiteParameter parameter6 = new SQLiteParameter("@executed");
            parameter6.DbType = DbType.DateTime;
            command.Parameters.Add(parameter6);
            SQLiteParameter parameter7 = new SQLiteParameter("@expired");
            parameter7.DbType = DbType.DateTime;
            command.Parameters.Add(parameter7);
            return command;
        }

        public static void DeleteConnectionStrings(List<int> idArray)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        StringBuilder builder = new StringBuilder();
                        foreach (int num in idArray)
                        {
                            if (builder.Length > 0)
                            {
                                builder.Append(",");
                            }
                            builder.Append(num);
                        }
                        command.CommandText = string.Format("Delete from `connectionstrings` where `id` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                        command.CommandText = string.Format("update `sqlitems` set `connectionstringid` = 0 where `connectionstringid` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteConnectionStringsByParentId(int pParentID)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = string.Format("Delete from `connectionstrings` where `parentid` =  {0}  ", pParentID);
                        command.ExecuteNonQuery();
                        command.CommandText = string.Format("update `sqlitems` set `connectionstringid` = 0 where `parentid` =  {0}  ", pParentID);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteGroups(List<int> idArray)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        StringBuilder builder = new StringBuilder();
                        foreach (int num in idArray)
                        {
                            if (builder.Length > 0)
                            {
                                builder.Append(",");
                            }
                            builder.Append(num);
                        }
                        command.CommandText = string.Format("Delete from `groups` where `id` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                        command.CommandText = string.Format("Delete from `sqlitems` where `parentid` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                        command.CommandText = string.Format("Delete from `sources` where `parentid` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                        command.CommandText = string.Format("Delete from `logs` where `parentid` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteLogsByParentId(int pParentID)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = string.Format("Delete from `logs` where `parentid` =  {0}  ", pParentID);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteSources(List<int> idArray)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        StringBuilder builder = new StringBuilder();
                        foreach (int num in idArray)
                        {
                            if (builder.Length > 0)
                            {
                                builder.Append(",");
                            }
                            builder.Append(num);
                        }
                        command.CommandText = string.Format("Delete from `sources` where `id` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteSourcesByParentId(int pParentID)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = string.Format("Delete from `sources` where `parentid` =  {0}  ", pParentID);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteSQLItems(List<int> idArray)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        StringBuilder builder = new StringBuilder();
                        foreach (int num in idArray)
                        {
                            if (builder.Length > 0)
                            {
                                builder.Append(",");
                            }
                            builder.Append(num);
                        }
                        command.CommandText = string.Format("Delete from `sqlitems` where `id` in ( {0} )", builder);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static void DeleteSQLItemsByParentId(int pParentID)
        {
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = string.Format("Delete from `sqlitems` where `parentid` =  {0}  ", pParentID);
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                connection.Close();
            }
        }

        public static string DumpDB(string pUser, string pPassword, string pDataBase, string pSQLFileName, REVISIONMAX20.Log pErrorLog, REVISIONMAX20.Log pOutLog)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString() + "_run.bat";
            string str2 = string.Format("\"{0}{1}\"", MySQLBinPath, "mysqldump.exe");
            string str3 = string.Format(" {2} --user={0} --password={1} --result-file=\"{3}\"", new object[] { pUser, pPassword, pDataBase, pSQLFileName });
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(str2 + str3);
            if (PauseBatch)
            {
                builder.AppendLine("pause");
            }
            File.WriteAllText(path, builder.ToString());
            return ExecuteBatch(path, true, pErrorLog, pOutLog);
        }

        public static string ExecuteBatch(string pBatchFilePath, bool pWaitForBatchCompletion, REVISIONMAX20.Log pErrorLog, REVISIONMAX20.Log pOutLog)
        {
            string str2;
            string str = "";
            Process process = new Process();
            try
            {
                process.StartInfo.ErrorDialog = false;
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                process.StartInfo.FileName = pBatchFilePath;
                process.StartInfo.RedirectStandardError = true;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.StandardErrorEncoding = Encoding.Default;
                process.StartInfo.StandardOutputEncoding = Encoding.Default;
                process.Start();
                if (pWaitForBatchCompletion)
                {
                    process.WaitForExit();
                }
            }
            catch (Exception exception)
            {
                if (process != null)
                {
                    process.Kill();
                }
                str = str + exception.Message + "\r\n";
            }
            StreamReader standardError = process.StandardError;
            StreamReader standardOutput = process.StandardOutput;
            StringBuilder builder = new StringBuilder();
            while (!standardError.EndOfStream)
            {
                str2 = standardError.ReadLine().Trim();
                if (str2.Length != 0)
                {
                    builder.AppendLine(str2);
                }
            }
            pErrorLog.value = builder.ToString();
            builder = new StringBuilder();
            while (!standardOutput.EndOfStream)
            {
                str2 = standardOutput.ReadLine().Trim();
                if (str2.Length != 0)
                {
                    builder.AppendLine(str2);
                }
            }
            pOutLog.value = builder.ToString();
            return (str + standardError.ReadToEnd());
        }

        public static string ExecuteBatch(string pUser, string pPassword, string pDataBase, string pSQLFileName, REVISIONMAX20.Log pErrorLog, REVISIONMAX20.Log pOutLog)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString() + "_run.bat";
            string str2 = string.Format("\"{0}{1}\"", MySQLBinPath, "mysql.exe");
            string str3 = string.Format(" --user={0} --password={1} --database={2} --show-warnings <\"{3}\"", new object[] { pUser, pPassword, pDataBase, pSQLFileName });
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(str2 + str3);
            if (PauseBatch)
            {
                builder.AppendLine("pause");
            }
            File.WriteAllText(path, builder.ToString());
            return ExecuteBatch(path, true, pErrorLog, pOutLog);
        }

        public static string ExecuteViaMYSQL(string file, string connectionstring, REVISIONMAX20.Log pErrorLog, REVISIONMAX20.Log pOutLog)
        {
            StringBuilder builder = new StringBuilder();
            using (StreamReader reader = new StreamReader(file))
            {
                using (MySqlConnection connection = new MySqlConnection(connectionstring))
                {
                    connection.Open();
                    using (MySqlTransaction transaction = connection.BeginTransaction())
                    {
                        while (!reader.EndOfStream)
                        {
                            using (MySqlCommand command = connection.CreateCommand())
                            {
                                string str = reader.ReadLine();
                                command.CommandType = CommandType.Text;
                                command.CommandText = str;
                                try
                                {
                                    if (command.ExecuteNonQuery() <= 0)
                                    {
                                        builder.AppendLine(string.Format("Warning: {0}, {1}", "[record not inserted]", str));
                                    }
                                }
                                catch (Exception exception)
                                {
                                    builder.AppendLine(string.Format("Error: {0}, {1}", exception.Message, str));
                                }
                            }
                        }
                        transaction.Commit();
                    }
                    connection.Close();
                }
                reader.Close();
            }
            if (builder.Length > 0)
            {
                pErrorLog.value = builder.ToString();
            }
            return builder.ToString();
        }

        public static string GetCommandByTableNameAndStatus(string pTableName, Status pStatus)
        {
            string str2 = pTableName;
            if (str2 != null)
            {
                if (!(str2 == "sqlitems"))
                {
                    if (str2 == "groups")
                    {
                        switch (pStatus)
                        {
                            case Status.StatusNew:
                                return _GroupInsertCommand;

                            case Status.StatusPopulated:
                                goto Label_017E;

                            case Status.StatusModified:
                                return _GroupUpdateCommand;

                            case Status.StatusDeleted:
                                return _GroupDeleteCommand;
                        }
                    }
                    else if (str2 == "connectionstrings")
                    {
                        switch (pStatus)
                        {
                            case Status.StatusNew:
                                return _ConnectionStringInsertCommand;

                            case Status.StatusPopulated:
                                goto Label_017E;

                            case Status.StatusModified:
                                return _ConnectionStringUpdateCommand;

                            case Status.StatusDeleted:
                                return _ConnectionStringDeleteCommand;
                        }
                    }
                    else if (str2 == "logs")
                    {
                        switch (pStatus)
                        {
                            case Status.StatusNew:
                                return _LogInsertCommand;

                            case Status.StatusDeleted:
                                return _LogDeleteCommand;
                        }
                    }
                    else if (str2 == "sources")
                    {
                        switch (pStatus)
                        {
                            case Status.StatusNew:
                                return _SourceInsertCommand;

                            case Status.StatusPopulated:
                                goto Label_017E;

                            case Status.StatusModified:
                                return _SourceUpdateCommand;

                            case Status.StatusDeleted:
                                return _SourceDeleteCommand;
                        }
                    }
                }
                else
                {
                    switch (pStatus)
                    {
                        case Status.StatusNew:
                            return _SQLItemInsertCommand;

                        case Status.StatusPopulated:
                            goto Label_017E;

                        case Status.StatusModified:
                            return _SQLItemUpdateCommand;

                        case Status.StatusDeleted:
                            return _SQLItemDeleteCommand;
                    }
                }
            }
        Label_017E:
            return "";
        }

        public static ConnectionString GetConnectionStringById(int id, ObjectBase owner)
        {
            ConnectionString str = null;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from `connectionstrings` where `id` = {0}", id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        str = new ConnectionString(reader, owner);
                    }
                }
                connection.Close();
            }
            return str;
        }

        public static DataTable GetConnectionStringDataTable(int parentid)
        {
            DataTable table = new DataTable("ConnectionString");
            table.Columns.Add("id");
            table.Columns.Add("name");
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from `connectionstrings` where `parentid` = {0}", parentid);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        table.Rows.Add(new object[] { reader.GetValue(0), reader.GetValue(4) });
                    }
                }
            }
            return table;
        }

        public static SortedList<int, string> GetConnectionStringIdNameList(ObjectBase owner)
        {
            SortedList<int, string> list = new SortedList<int, string>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from connectionstrings where parentid = {0} order by name", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        ConnectionString str = new ConnectionString(reader, owner);
                        list.Add(str.id, str.name);
                    }
                }
            }
            return list;
        }

        public static SortedList<int, string> GetConnectionStringIdValueList(ObjectBase owner)
        {
            SortedList<int, string> list = new SortedList<int, string>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from connectionstrings where parentid = {0} order by name", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        ConnectionString str = new ConnectionString(reader, owner);
                        list.Add(str.id, str.value);
                    }
                }
            }
            return list;
        }

        public static List<ConnectionString> GetConnectionStringList(ObjectBase owner)
        {
            List<ConnectionString> list = new List<ConnectionString>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from connectionstrings where parentid = {0} ", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        ConnectionString item = new ConnectionString(reader, owner);
                        list.Add(item);
                    }
                }
            }
            return list;
        }

        public static SortedList<string, int> GetConnectionStringNameIdList(ObjectBase owner)
        {
            SortedList<string, int> list = new SortedList<string, int>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from connectionstrings where parentid = {0} order by name", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        ConnectionString str = new ConnectionString(reader, owner);
                        if (!list.ContainsKey(str.name))
                        {
                            list.Add(str.name, str.id);
                        }
                    }
                }
            }
            return list;
        }

        public static Group GetGroupById(int id)
        {
            Group group = null;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from `groups` where `id` = {0}", id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        group = new Group(reader);
                    }
                }
                connection.Close();
            }
            return group;
        }

        public static REVISIONMAX20.Log GetLogById(int id, ObjectBase owner)
        {
            REVISIONMAX20.Log log = null;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from `logs` where `id` = {0}", id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        log = new REVISIONMAX20.Log(reader, owner);
                    }
                }
                connection.Close();
            }
            return log;
        }

        public static List<REVISIONMAX20.Log> GetLogList(ObjectBase owner)
        {
            List<REVISIONMAX20.Log> list = new List<REVISIONMAX20.Log>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from logs where parentid = {0} order by `created`", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        list.Add(new REVISIONMAX20.Log(reader, owner));
                    }
                }
                connection.Close();
            }
            return list;
        }

        public static Source GetSourceById(int id, ObjectBase owner)
        {
            Source source = null;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from `sources` where `id` = {0}", id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        source = new Source(reader, owner);
                    }
                }
                connection.Close();
            }
            return source;
        }

        public static List<Source> GetSourceList(ObjectBase owner)
        {
            List<Source> list = new List<Source>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from sources where parentid = {0}", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        list.Add(new Source(reader, owner));
                    }
                }
            }
            return list;
        }

        public static DataTable GetSourceTypeDataTable()
        {
            DataTable table = new DataTable("SourceType");
            table.Columns.Add("id");
            table.Columns.Add("name");
            table.Rows.Add(new object[] { "", "none" });
            table.Rows.Add(new object[] { "sqlfile", "SQL File" });
            table.Rows.Add(new object[] { "sourcetext", "Source Text" });
            return table;
        }

        public static SQLiteConnection GetSQLiteConnection()
        {
            if (!File.Exists(SQLiteDBName))
            {
                SQLiteConnection.CreateFile(SQLiteDBName);
                using (SQLiteConnection connection = new SQLiteConnection(GetSQLiteConnectionString()))
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        string str = "`id` integer PRIMARY KEY, `created` datetime, `updated` datetime";
                        string str2 = "`id` integer PRIMARY KEY, `parentid` integer, `created` datetime, `updated` datetime";
                        string str3 = "create table `nextid` (`id` integer, `name` string  PRIMARY KEY);";
                        string str4 = string.Format("create table `groups` ({0}, `name` string, `lastrevision` string);", str);
                        string str5 = string.Format("create table `connectionstrings` ({0}, `name` string, `value` string);", str2);
                        string str6 = string.Format("create table `sqlitems` ({0}, `index` integer, `revision` string, `source` string, `sourcetype` string, `connectionstringid` integer, `executed` datetime);", str2);
                        string str7 = string.Format("create table `logs` ({0}, `value` string);", str2);
                        string str8 = string.Format("create table `sources` ({0}, `source` string, `regex` string, `recursive` BOOLEAN);", str2);
                        StringBuilder builder = new StringBuilder();
                        builder.AppendLine(str3);
                        builder.AppendLine(str4);
                        builder.AppendLine(str6);
                        builder.AppendLine(str5);
                        builder.AppendLine(str7);
                        builder.AppendLine(str8);
                        builder.AppendLine("insert into nextid values(0, 'groups');");
                        builder.AppendLine("insert into nextid values(0, 'sqlitems');");
                        builder.AppendLine("insert into nextid values(0, 'connectionstrings');");
                        builder.AppendLine("insert into nextid values(0, 'logs');");
                        builder.AppendLine("insert into nextid values(0, 'sources');");
                        using (SQLiteCommand command = connection.CreateCommand())
                        {
                            command.CommandText = builder.ToString();
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
            }
            return new SQLiteConnection(GetSQLiteConnectionString());
        }

        public static string GetSQLiteConnectionString()
        {
            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            builder.DataSource = SQLiteDBName;
            return builder.ConnectionString;
        }

        public static SQLItem GetSQLItemById(int id, ObjectBase owner)
        {
            SQLItem item = null;
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from `sqlitems` where `id` = {0}", id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        item = new SQLItem(reader, owner);
                    }
                }
                connection.Close();
            }
            return item;
        }

        public static SQLItem GetSQLItemById(int id, ObjectBase owner, SQLiteConnection con)
        {
            SQLItem item = null;
            using (SQLiteCommand command = con.CreateCommand())
            {
                command.CommandText = string.Format("select * from `sqlitems` where `id` = {0}", id);
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    item = new SQLItem(reader, owner);
                }
            }
            return item;
        }

        public static List<SQLItem> GetSQLItemList(ObjectBase owner)
        {
            List<SQLItem> list = new List<SQLItem>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from sqlitems where parentid = {0} ", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        SQLItem item = new SQLItem(reader, owner);
                        list.Add(item);
                    }
                }
            }
            return list;
        }

        public static List<SQLItem> GetSQLItemListForExecution(ObjectBase owner)
        {
            List<SQLItem> list = new List<SQLItem>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from sqlitems where `parentid` = {0} and `executed` is null and not `revision` is null and not `index` is null and not `index` = '0' and `expired` is null  order by `revision`, `index`", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        list.Add(new SQLItem(reader, owner));
                    }
                }
            }
            return list;
        }

        public static SortedList<string, SQLItem> GetSQLItemSQLFileList(ObjectBase owner)
        {
            SortedList<string, SQLItem> list = new SortedList<string, SQLItem>();
            using (SQLiteConnection connection = GetSQLiteConnection())
            {
                connection.Open();
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("select * from sqlitems where parentid = {0} and `sourcetype` = 'sqlfile'", owner.id);
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        SQLItem item = new SQLItem(reader, owner);
                        list.Add(item.source, item);
                    }
                }
            }
            return list;
        }

        public static int GetTableRowCount(string pTableName)
        {
            int num = -1;
            SQLiteConnection sQLiteConnection = GetSQLiteConnection();
            sQLiteConnection.Open();
            using (SQLiteCommand command = sQLiteConnection.CreateCommand())
            {
                command.CommandText = string.Format("select count(*) from '{0}'", pTableName);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            num = reader.GetInt32(0);
                        }
                    }
                    return num;
                }
            }
        }

        public static int NextID(SQLiteConnection con, string pTableName)
        {
            int num = -1;
            using (SQLiteCommand command = con.CreateCommand())
            {
                command.CommandText = string.Format("update nextid set id = id + 1 where name='{0}'", pTableName);
                command.CommandType = CommandType.Text;
                if (command.ExecuteNonQuery() <= 0)
                {
                    return num;
                }
                command.CommandText = string.Format("select * from nextid where name = '{0}'", pTableName);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows && reader.Read())
                    {
                        num = reader.GetInt32(0);
                    }
                    return num;
                }
            }
        }

        public static string MySQLBinPath
        {
            get
            {
                return ConfigurationManager.AppSettings["MySQLBinPath"];
            }
            set
            {
                ConfigurationManager.AppSettings["MySQLBinPath"] = value;
            }
        }

        public static bool PauseBatch
        {
            get
            {
                return ConfigurationManager.AppSettings["PauseBatch"].BoolValue();
            }
            set
            {
                ConfigurationManager.AppSettings["PauseBatch"] = value.ToString();
            }
        }
    }
}

